package bcared;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class Engineer {
	
	//fields in the Engineers table
	private int engineerID;
	private int userID;
	private int SOID;
	private String cellNumber;

	//fields in the Users table
	private String userName;
	private int roleID;
	private String street;
	private String city;
	private int zip;
	private String firstName;
	private String lastName;
	
	//fields in the Roles table
	private String roleName;
	
	//fields from EngineersModalities
	private ArrayList<Integer> modalityIDArrayList = new ArrayList<Integer>();
	private ArrayList<String> modalityDescriptionArrayList = new ArrayList<String>();
	
	private ResultSet rs = null;
	private CallableStatement csmt = null;
	private PreparedStatement ps = null;
	private OraDB myDB = new OraDB();
	
	/**
	 * Constructs the Engineer based on engineerID
	 * @param engineerID
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */
	public Engineer(int engineerID) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
		this.engineerID = engineerID;
		populate();
	}
	
	/**
	 * Constructs the Engineer based on userID
	 * @param userID
	 * @param notUsed
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */
	public Engineer(int userID, int temp) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
		this.userID = userID;
		
		myDB.createConnection();
		String myQuery = "SELECT engineerID FROM Engineers"
				+ " INNER JOIN Users ON Users.userID = Engineers.userID"
				+ " WHERE Users.userID = ?";
		
		this.ps = myDB.conn.prepareStatement(myQuery);
		this.ps.setInt(1, this.userID);
		this.rs = this.ps.executeQuery();
		
		while (rs.next()) {
			this.engineerID = rs.getInt("engineerID");
		}
		
		myDB.closeConnection();
				
		populate();
	}
	
	public void populate() throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
		
		myDB.createConnection();
		String myQuery = "SELECT engineerID, Engineers.userID, SOID, cellNumber, username, Users.roleID,"
				+ " street, city, zip, firstName, lastName, roleName"
				+ " FROM Engineers"
				+ " INNER JOIN Users ON Users.userID = Engineers.userID"
				+ " INNER JOIN Roles ON Roles.roleID = Users.roleID"
				+ " WHERE engineerID = ?";
		
		this.ps = myDB.conn.prepareStatement(myQuery);
		this.ps.setInt(1, this.engineerID);
		this.rs = this.ps.executeQuery();
		
		while (rs.next()) {
			this.engineerID = rs.getInt("engineerID");
			this.userID = rs.getInt("userID");
			this.SOID = rs.getInt("SOID");
			this.cellNumber = rs.getString("cellNumber");
			this.userName = rs.getString("userName");
			this.roleID = rs.getInt("roleID");
			this.street = rs.getString("street");
			this.city = rs.getString("city");
			this.zip = rs.getInt("zip");
			this.firstName = rs.getString("firstName");
			this.lastName = rs.getString("lastName");
			this.roleName = rs.getString("roleName");
		}
		
		myQuery = "SELECT EngineersModalities.modalityID, modalityDescription"
				+ " FROM Engineers"
				+ " INNER JOIN EngineersModalities ON EngineersModalities.engineerID = Engineers.engineerID"
				+ " INNER JOIN Modality ON Modality.modalityID = EngineersModalities.modalityID"
				+ " WHERE Engineers.engineerID = ?";
		
		this.ps = myDB.conn.prepareStatement(myQuery);
		this.ps.setInt(1, this.engineerID);
		this.rs = this.ps.executeQuery();
		
		while (rs.next()) {
			this.modalityIDArrayList.add(rs.getInt("modalityID"));
			this.modalityDescriptionArrayList.add(rs.getString("modalityDescription"));
		}	
		
		myDB.closeConnection();
	}
	
	/**
	 * returns all of the modality descriptions as a comma delimited list for the engineer
	 * @return
	 */
	public String getModalityDescriptions() {
		String result = "";
		
		for (int i = 0; i < this.modalityDescriptionArrayList.size(); i++) {
			result = result + this.modalityDescriptionArrayList.get(i) + ", ";
		}

		result.substring(0, (result.length() - 2));
		
		return result;
	}
	
	public boolean isCertified(int modalityID) {
		return this.modalityIDArrayList.contains(modalityID);
	}
	
	
	public int getEngineerID() {
		return engineerID;
	}

	public int getUserID() {
		return userID;
	}

	public int getSOID() {
		return SOID;
	}

	public String getCellNumber() {
		return cellNumber;
	}

	public String getUserName() {
		return userName;
	}

	public int getRoleID() {
		return roleID;
	}

	public String getStreet() {
		return street;
	}

	public String getCity() {
		return city;
	}

	public int getZip() {
		return zip;
	}

	public String getFirstName() {
		return firstName;
	}

	public String getLastName() {
		return lastName;
	}

	public String getRoleName() {
		return roleName;
	}

	public ResultSet getRs() {
		return rs;
	}

	public CallableStatement getCsmt() {
		return csmt;
	}

	public PreparedStatement getPs() {
		return ps;
	}

	public OraDB getMyDB() {
		return myDB;
	}

	public void setEngineerID(int engineerID) {
		this.engineerID = engineerID;
	}

	public void setUserID(int userID) {
		this.userID = userID;
	}

	public void setSOID(int soid) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
		SOID = soid;
		myDB.createConnection();
		String myQuery = "update engineers set SOID = ? where engineerID = ?";
		
		ps = myDB.conn.prepareStatement(myQuery);
		ps.setInt(1, SOID);
		ps.setInt(2, this.engineerID);
		rs = ps.executeQuery();
		myDB.closeConnection();
		populate();
	}

	public void setCellNumber(String cellNumber) {
		this.cellNumber = cellNumber;
	}
	
	public void clearSOID() throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
		
		myDB.createConnection();
		String myQuery = "update engineers set SOID = NULL where engineerID = ?";
		
		ps = myDB.conn.prepareStatement(myQuery);
		ps.setInt(1, this.engineerID);
		rs = ps.executeQuery();
		myDB.closeConnection();
		populate();
	}
	

		
}
